﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using CrystalDecisions.CrystalReports.Engine;
using System.Configuration;

namespace UdrinkWater_Project.Admin
{
    public partial class ReportOrderMaterial1 : System.Web.UI.Page
    {
        private string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            DataTable dt = null;
            DateTime OrderMDate = (DateTime)Session["OrderM_Date"];
            string OrderM_Date = OrderMDate.ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
            StringBuilder strSQL = new StringBuilder();
            
            

            strSQL.Append(@"SELECT  ROW_NUMBER() OVER(ORDER BY TB_Order_Material.OrderM_Date asc) AS Row,TB_Order_Material.OrderM_Date, TB_Material.Materail_name, TB_Unit_Category.Unit_Name, TB_OrderMaterial_list.Material_ID, TB_OrderMaterial_list.Material_Count, TB_OrderMaterial_list.Price, 
                            ( TB_Order_Material.Address +' ตำบล '+ TB_District.DISTRICT_NAME +'อำเภอ '+ TB_Aumphur.AMPHUR_NAME +'จังหวัด '+ TB_Province.PROVINCE_NAME ) As Address
                            FROM          TB_Order_Material INNER JOIN
                            TB_OrderMaterial_list ON TB_Order_Material.OrderM_Date = TB_OrderMaterial_list.OrderM_Date INNER JOIN
                            TB_Aumphur ON TB_Order_Material.Aumphur_ID = TB_Aumphur.AMPHUR_ID INNER JOIN
                            TB_District ON TB_Order_Material.District_ID = TB_District.DISTRICT_ID INNER JOIN
                            TB_Province ON TB_Order_Material.Province_ID = TB_Province.PROVINCE_ID INNER JOIN
                            TB_Material ON TB_OrderMaterial_list.Material_ID = TB_Material.Material_ID INNER JOIN
                            TB_Unit_Category ON TB_Material.Unit = TB_Unit_Category.Unit_ID");
            strSQL.Append(" WHERE TB_Order_Material.OrderM_Date = '" + OrderM_Date + "'");




            objConn.ConnectionString = Connection;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds, "ReportOrderMaterial");
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            ReportDocument rpt = new ReportDocument();
            rpt.Load(Server.MapPath("ReportOrderMaterial.rpt"));

            rpt.SetDataSource(dt);


          /*  rpt.SetParameterValue("list", list);
            rpt.SetParameterValue("SumAll", SumAll);*/

            //this.ShopOrderReport.ReportSource = rpt;
            rpt.ExportToHttpResponse
            (CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "OrderMaterial " + OrderM_Date);
        }
    }
}